package com.gack.business.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.gack.business.model.User;
import com.gack.business.vo.UserItemVO;

@Repository
public interface UserRepository extends JpaRepository<User, String>,JpaSpecificationExecutor<User>{

	@Query("select u.id"
			+ " from User u"
			+ " where u.username=:username")
	String findIdByUsername(@Param("username")String username);//根据用户名查询id
	
	List<User> findByUsername(String username);//根据用户名查询用户
	
	@Query("select new com.gack.business.vo.UserItemVO(u.id, u.username, u.portrait, u.nickname)"
			+ " from User u"
			+ " where u.id in (select uedp.userId"
								+ " from UserEnterpriseDepartmentPosition uedp"
								+ " where uedp.enterpriseId=:enterpriseId)"
			+ " and u.nickname like %:inputName%")
	List<UserItemVO> findUserItemVOByInputName(@Param("enterpriseId") String enterpriseId, @Param("inputName") String inputName);//查询该公司的用户昵称中匹配该输入名称的用户ItemVO(id, username, portrait, 昵称)
	
	@Query("select new com.gack.business.vo.UserItemVO(u.id, u.username, u.portrait, u.nickname)"
			+ " from User u"
			+ " where u.id=:userId")
	UserItemVO findUserItemVOById(@Param("userId") String userId);//查询该用户的ItemVO(id, username, portrait, 昵称)
	
	@Query("select new com.gack.business.vo.UserItemVO(u.id, u.username, u.portrait, u.nickname)"
			+ " from User u"
			+ " where u.id in (select uedp.userId"
								+ " from UserEnterpriseDepartmentPosition uedp"
								+ " where uedp.enterpriseId=:enterpriseId "
								+ " and (uedp.departmentId = null or uedp.departmentId = '') )")
	List<UserItemVO> findNoDepartmentUserItemVO(@Param("enterpriseId") String enterpriseId);//查询无部门用户ItemVO(id, username, portrait, 昵称)
	
	@Query("select u from User u   where u.username=:username")
	User findUserforUsername(@Param("username")String username);

	@Query("select new com.gack.business.vo.UserItemVO(u.id, u.username, u.portrait, u.nickname)"
			+ " from User u"
			+ " where u.id in (select uedp.userId"
								+ " from UserEnterpriseDepartmentPosition uedp"
								+ " where uedp.departmentId=:departmentId) ")
	List<UserItemVO> findUserItemVOByDepartmentId(@Param("departmentId") String departmentId);//查询当前部门下人员ItemVO(不包含子部门的人员ItemVO)
	

	List<User> findByIsPledge(int isPledge);

	@Query("select u.nickname"
			+ " from User u"
			+ " where u.id=:userId")
	String findNicknameById(@Param("userId") String userId);//根据用户id查询用户昵称

	
	@Query("select u.portrait"
			+ " from User u"
			+ " where u.id=:userId")
	String findPortraitById(@Param("userId") String userId);//根据用户id查询用户头像
	
	@Query("select u.username"
			+ " from User u"
			+ " where u.id=:userId")
	String findUsernameById(@Param("userId") String userId);//根据用户id查询用户手机号
	
	@Query("select u from User u where u.username = :username and u.password = :password")
	User findUserByUsernameAndPassword(@Param("username") String username ,@Param("password")String password);
	
	@Transactional
	@Modifying
	@Query("update User u set u.loginNum = u.loginNum + 1 where u.id = :id")
	public Integer addOneLoginNum(@Param("id") String id);
	
	@Query("select u from User u where u.username = :username")
	List<User> findUserByUsername(@Param("username") String username );
	
	@Query(value = "select u.portrait,"
			+ " (select department_id from user_enterprise_department_position where enterprise_id=:enterpriseId and user_id=:userId),"
			+ " (select name from position where id=(select position_id from user_enterprise_department_position where enterprise_id=:enterpriseId and user_id=:userId)),"
			+ " (select username from user where id=:userId),"
			+ " case when (select friend_remarks"
			+ " from contacts" 
			+ " where user_username=(select username from user where id=:operatorId)"
			+ " and userid_friend_id=:userId"
			+ " and type=1) is not null"
			+ " and (select trim(friend_remarks)"
				+ " from contacts" 
				+ " where user_username=(select username from user where id=:operatorId)"
				+ " and userid_friend_id=:userId"
				+ " and type=1) != ''"
			+ " then (select friend_remarks"
					+ " from contacts" 
					+ " where user_username=(select username from user where id=:operatorId)"
					+ " and userid_friend_id=:userId"
					+ " and type=1)"
			+ " else (select enterprise_remarks"
						+ " from user_enterprise_department_position"
						+ " where enterprise_id=:enterpriseId"
						+ " and user_id=:userId)"
			+ " end as name_for_show"
			+ " from user u"
			+ " where u.id=:userId", nativeQuery = true) 
	List<Object[]> findPortraitAndDepartmentIdAndPositionNameAndUsernameAndNameForShow(@Param("enterpriseId")String enterpriseId, @Param("userId")String userId, @Param("operatorId")String operatorId);
	
}
